<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE topic
  PUBLIC "-//OASIS//DTD DITA Composite//EN" "ditabase.dtd">
<topic id="topic1"><title id="ee20941">ROLLBACK TO SAVEPOINT</title><body><p id="sql_command_desc">Rolls back the current transaction to a savepoint.</p><section id="section2"><title>Synopsis</title><codeblock id="sql_command_synopsis">ROLLBACK [WORK | TRANSACTION] TO [SAVEPOINT] <varname>savepoint_name</varname></codeblock></section><section id="section3"><title>Description</title><p>This command will roll back all commands that were run after the
savepoint was established. The savepoint remains valid and can be rolled
back to again later, if needed. </p><p><codeph>ROLLBACK TO SAVEPOINT</codeph> implicitly destroys all savepoints
that were established after the named savepoint.</p></section><section id="section4"><title>Parameters</title><parml><plentry><pt>WORK</pt><pt>TRANSACTION</pt><pd>Optional key words. They have no effect.</pd></plentry><plentry><pt><varname>savepoint_name</varname></pt><pd>The name of a savepoint to roll back to.</pd></plentry></parml></section><section id="section5"><title>Notes</title><p>Use <codeph>RELEASE SAVEPOINT</codeph> to destroy a savepoint without
discarding the effects of commands run after it was established.
</p><p>Specifying a savepoint name that has not been established is an error.</p><p>Cursors have somewhat non-transactional behavior with respect to savepoints. Any cursor that is
                                opened inside a savepoint will be closed when the savepoint is
                                rolled back. If a previously opened cursor is affected by a
                                        <codeph>FETCH</codeph> command inside a savepoint that is
                                later rolled back, the cursor remains at the position that
                                        <codeph>FETCH</codeph> left it pointing to (that is, cursor
                                motion caused by <codeph>FETCH</codeph> is not rolled back). Closing
                                a cursor is not undone by rolling back, either. However, other
                                side-effects caused by the cursor's query (such as side-effects of
                                volatile functions called by the query) are rolled back if they
                                occur during a savepoint that is later rolled back. A cursor whose
                                execution causes a transaction to end prematurely is put in a
                                cannot-execute state, so while the transaction can be restored using
                                        <codeph>ROLLBACK TO SAVEPOINT</codeph>, the cursor can no
                                longer be used.</p></section><section id="section6"><title>Examples</title><p>To undo the effects of the commands run after <codeph>my_savepoint</codeph> was
                                established:</p><codeblock>ROLLBACK TO SAVEPOINT my_savepoint;</codeblock><p>Cursor positions are not affected by a savepoint rollback:</p><codeblock>BEGIN;
DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2;
SAVEPOINT foo;
FETCH 1 FROM foo;
column 
----------
        1
ROLLBACK TO SAVEPOINT foo;
FETCH 1 FROM foo;
column 
----------
        2
COMMIT;</codeblock></section><section id="section7"><title>Compatibility</title><p>The SQL standard specifies that the key word <codeph>SAVEPOINT</codeph>
is mandatory, but Greenplum Database (and Oracle) allow it to be omitted.
SQL allows only <codeph>WORK</codeph>, not <codeph>TRANSACTION</codeph>,
as a noise word after <codeph>ROLLBACK</codeph>. Also, SQL has an optional
clause <codeph>AND [NO] CHAIN</codeph> which is not currently supported
by Greenplum Database. Otherwise, this command conforms to the SQL standard.
</p></section><section id="section8"><title>See Also</title><p><codeph><xref href="BEGIN.xml#topic1" type="topic" format="dita"/></codeph>, <codeph><xref
                                                href="COMMIT.xml#topic1" type="topic" format="dita"
                                        /></codeph>, <codeph><xref href="./SAVEPOINT.xml#topic1"
                                                type="topic" format="dita"/></codeph>, <codeph><xref
                                                href="RELEASE_SAVEPOINT.xml#topic1" type="topic"
                                                format="dita"/></codeph>, <codeph><xref
                                                href="ROLLBACK.xml#topic1" type="topic"
                                                format="dita"/></codeph></p></section></body></topic>
